Imports System.Data
Imports System.Data.SqlClient
Imports Conexao_liion

Partial Class Departamento
    Inherits System.Web.UI.Page
    Dim blnGridSelectedRow As Boolean = False

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        Response.Cache.SetCacheability(HttpCacheability.ServerAndNoCache)

        btnAlterar.Enabled = False
        btnExcluir.Enabled = False

        CarregaGVW()

    End Sub

    Private Sub CarregaGVW()

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim mydatatable As New DataTable

            Dim theQueryString As String = "SELECT A.CodigoDaHolding, A.CodigoDaCia, C.NomeDaDiretoria, A.CodigoDaDiretoria, B.NomeDaDivisao, A.CodigoDaDivisao, A.NomeDoDepartamento, A.CodigoDoDepartamento, A.CodigoDeCusto " & _
                "FROM Departamento A, Divisao B, Diretoria C " & _
                "WHERE A.CodigoDaHolding = C.CodigoDaHolding AND " & _
                "A.CodigoDaCia = C.CodigoDaCia AND " & _
                "A.CodigoDaDiretoria = C.CodigoDaDiretoria AND " & _
                "A.CodigoDaHolding = B.CodigoDaHolding AND " & _
                "A.CodigoDaCia = B.CodigoDaCia AND " & _
                "A.CodigoDaDiretoria = B.CodigoDaDiretoria AND " & _
                "A.CodigoDaDivisao = B.CodigoDaDivisao " & _
                "ORDER BY NomeDaDiretoria, NomeDaDivisao, NomeDoDepartamento;"


            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                mydatatable.Load(theSqlDataReader)
                gvwData.DataSource = mydatatable
                gvwData.DataBind()

            Catch ex As Exception
                'theSqlDataReader.Close()
                'lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using

    End Sub

    Private Function VerificaDado(ByVal strConn As String) As Boolean


        VerificaDado = False

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim mydatatable As New DataTable
            Dim theQueryString As String = "SELECT NomeDoDepartamento" & _
                     " FROM CentroDeCusto " & _
                     " WHERE CodigoDaHolding = '" & HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(0).Text) & "'" & _
                     " AND CodigoDaCia = '" & HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(1).Text) & "'" & _
                     " AND NomeDaDiretoria = '" & HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(2).Text) & "'" & _
                     " AND NomeDaDivisao = '" & HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(3).Text) & _
                     " AND NomeDoDepartamento = '" & HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(4).Text) & "';"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()
                If theSqlDataReader.Read() Then
                    VerificaDado = True
                End If
                theSqlDataReader.Close()
            Catch ex As Exception
                Console.WriteLine(ex.Message)
                'theSqlDataReader.Close()
            End Try

        End Using

    End Function

    Private Function ExcluiDepartamento(ByVal strConn As String) As Boolean

        ExcluiDepartamento = False

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim mydatatable As New DataTable
            Dim theQueryString As String = "DELETE FROM Departamento " & _
                     " WHERE CodigoDaHolding = '" & HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(0).Text) & "'" & _
                     " AND CodigoDaCia = '" & HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(1).Text) & "'" & _
                     " AND NomeDaDiretoria = '" & HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(2).Text) & "'" & _
                     " AND NomeDaDivisao = '" & HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(3).Text) & "'" & _
                     " AND NomeDoDepartamento = '" & HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(4).Text) & "';"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()
                theSqlDataReader.Close()
                ExcluiDepartamento = True
            Catch ex As Exception
                'theSqlDataReader.Close()
                Console.WriteLine(ex.Message)
            End Try

        End Using

    End Function

    Protected Sub gvwData_PageIndexChanging(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewPageEventArgs) Handles gvwData.PageIndexChanging

        gvwData.PageIndex = e.NewPageIndex
        gvwData.DataBind()

    End Sub

    Protected Sub gvwData_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles gvwData.SelectedIndexChanged

        btnAlterar.Enabled = True
        btnExcluir.Enabled = True

    End Sub



    Protected Sub btnIncluir_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnIncluir.Click

        Session.Add("strOperacao", "I") ' Incluir

        Session.Add("strCodigoDaHolding", "")
        Session.Add("strCodigoDaCia", "")
        Session.Add("strNomeDaDiretoria", "")
        Session.Add("strCodigoDaDiretoria", "")
        Session.Add("strNomeDaDivisao", "")
        Session.Add("strCodigoDaDivisao", "")
        Session.Add("strNomeDoDepartamento", "")
        Session.Add("strCodigoDoDepartamento", "")
        Session.Add("strCodigoDeCusto", "")

        Response.Redirect("DepartamentoData.aspx")

    End Sub

    Protected Sub btnAlterar_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnAlterar.Click

        Session.Add("strOperacao", "A") ' Alterar

        Session.Add("strCodigoDaHolding", HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(0).Text))
        Session.Add("strCodigoDaCia", HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(1).Text))
        Session.Add("strNomeDaDiretoria", HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(2).Text))
        Session.Add("strCodigoDaDiretoria", HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(3).Text))
        Session.Add("strNomeDaDivisao", HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(4).Text))
        Session.Add("strCodigoDaDivisao", HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(5).Text))
        Session.Add("strNomeDoDepartamento", HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(6).Text))
        Session.Add("strCodigoDoDepartamento", HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(7).Text))
        Session.Add("strCodigoDeCusto", HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(8).Text))

        Response.Redirect("DepartamentoData.aspx")

    End Sub

    Protected Sub btnExcluir_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnExcluir.Click

        Dim ConnectionTest As String
        ConnectionTest = "A"


        If (VerificaDado(ConnectionTest) = True) Then
            Exit Sub
        End If

        If (ExcluiDepartamento(ConnectionTest) = False) Then
            Exit Sub
        End If

        Response.Redirect("Departamento.aspx")

    End Sub

    Protected Sub btnVoltar_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnVoltar.Click

        Response.Redirect("Default.aspx")

    End Sub

End Class
